package zhuhai.tedu.parttimess.service.impl;

import com.sun.deploy.net.HttpResponse;
import io.swagger.models.auth.In;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;
import zhuhai.tedu.parttimess.domain.Result;
import zhuhai.tedu.parttimess.domain.Student;
import zhuhai.tedu.parttimess.domain.User;
import zhuhai.tedu.parttimess.repository.BaseRepository;
import zhuhai.tedu.parttimess.repository.StudentRepository;
import zhuhai.tedu.parttimess.service.StudentService;
import zhuhai.tedu.parttimess.utils.ExcelUtils;
import zhuhai.tedu.parttimess.utils.StringUtils;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


@Service
public class StudentServiceImpl  extends CommonServiceImpl<Student,Integer> implements StudentService {

    @Autowired
    private StudentRepository studentRepository;
    @Override
    public BaseRepository<Student, Integer> currentRepository() {
        return studentRepository;
    }

    @Override
    public Page<Student> findByPage(Student student, Pageable pageable) {
        return studentRepository.findAll(new Specification<Student>() {
            @Override
            public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder cb) {
                String name = student.getName();
                List<Predicate> predicates = new ArrayList<>();
                if (name != null) {
                    predicates.add(cb.like(root.get("name"), name));
                }
                User user = student.getUser();
                if (user != null){
                    Integer userId=user.getId();
                    String userName=user.getName();
                    if(null!=userId){
                        predicates.add(cb.equal(root.get("user").get("id"),userId));
                    }
                   if (userName!=null){
                       predicates.add(cb.like(root.get("user").get("name"),userName));
                   }

            }
            return cb.and(predicates.toArray(new Predicate[predicates.size()]));}
        },pageable);
    }

    //导入学生数据
    @Override
    public Result importStudentData(MultipartFile file) {
        System.out.println("进入导入方法");
        List<Student> studentList=new ArrayList<>();
        Workbook workbook=null;
        String fileName=file.getOriginalFilename();
        try {
            if (ExcelUtils.isExcel2007(fileName)) {
                workbook = new XSSFWorkbook(file.getInputStream());
            } else {
                workbook = new HSSFWorkbook(file.getInputStream());
            }
            Sheet sheet = workbook.getSheetAt(0);
            int max=sheet.getLastRowNum()+1;
            for (int r=1;r<max;r++){
                Student student=new Student();
                Row row=sheet.getRow(r);
                //获取月报日期
                Date montylyDate;
                if(null!=row.getCell(4)){
                    montylyDate=row.getCell(4).getDateCellValue();
                }else {
                    montylyDate=null;
                }
                //产品线
                String productLine;
                if(null!=row.getCell(5)){
                    productLine=row.getCell(5).getStringCellValue();
                }else {
                    productLine=null;
                }
                //系列班级
                String className;
                if(null!=row.getCell(6)){
                    className=row.getCell(6).getStringCellValue();
                }else {
                    className=null;
                }
                //姓名
                String name;
                if (null!=row.getCell(7)){
                    name=row.getCell(7).getStringCellValue();
                }else {
                    name=null;
                }
                //性别
                String sex;
                if (null!=row.getCell(8)){
                    sex=row.getCell(8).getStringCellValue();
                }else {
                    sex=null;
                }
                //学习地点
                String studyLocation;
                if(null!=row.getCell(9)){
                    studyLocation=row.getCell(9).getStringCellValue();
                }else {
                    studyLocation=null;
                }
                //负责项目经理
                String manager;
                if(null!=row.getCell(10))
                {
                    manager=row.getCell(10).getStringCellValue();
                }else {
                    manager=null;
                }
                //班主任
                String headermaster;
                if(null!=row.getCell(11)){
                    headermaster=row.getCell(11).getStringCellValue();
                }else {
                    headermaster=null;
                }
                //学费总值
                Double totalFee;
                if(null!=row.getCell(12)){
                    totalFee=Double.valueOf(row.getCell(12).getNumericCellValue());
                }else {
                    totalFee=null;
                }
                //课期数
                Double classAmount;
                if(null!=row.getCell(13)){
                    classAmount=Double.valueOf(row.getCell(13).getNumericCellValue());
                }else {
                    classAmount=null;
                }

                //目前状态
                String status;
                if(null!=row.getCell(14)){
                    status=row.getCell(14).getStringCellValue();
                }else {
                    status=null;
                }
                //咨询顾问
                String consultant;
                if(null!=row.getCell(15)){
                    consultant=row.getCell(15).getStringCellValue();
                }else {
                    consultant=null;
                }
                //QQ号
                Long qq;
                if(null!=row.getCell(16)){
                    Double doou=row.getCell(16).getNumericCellValue();
                    qq=doou.longValue();
                }else {
                    qq=null;
                }

                //联系电话
                Long phone;
                if(null!=row.getCell(17)){
                    Double dou=row.getCell(17).getNumericCellValue();
                    phone=dou.longValue();
                    System.out.println(phone);
                }else {
                    phone=null;
                }
                student.setClassAmount(classAmount);
                student.setClassName(className);
                student.setConsultant(consultant);
                student.setHeadmaster(headermaster);
                student.setManager(manager);
                student.setMonthlyDate(montylyDate);
                student.setName(name);
                student.setPhone(phone);
                student.setProductLine(productLine);
                student.setQq(qq);
                student.setSex(sex);
                student.setStatus(status);
                student.setStudyLocation(studyLocation);
                student.setTotalFee(totalFee);
                System.out.println(student.toString());
                studentRepository.save(student);
            }
        }catch (IOException e){
            e.printStackTrace();
        }
        return new Result("添加成功",1);
    }


    //导出学生信息
    public Result exportSutdentData(Integer userId,HttpServletResponse response) {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet("信息表");

            List<Student> studentList = studentRepository.findByUserID(userId);
            String fileName = "student" + ".xls";//设置要导出的文件的名
            //新增数据行，并且设置单元格数据

            int rowNum = 1;

            String[] headers = {"姓名","产品线", "班级名","状态","性别", "电话","QQ号",
                    "项目经理","班主任","咨询师","学习地点","总费用","月报日期","课期数"};
            //headers表示excel表中第一行的表头

            HSSFRow row = sheet.createRow(0);
            //在excel表中添加表头

            for (int i = 0; i < headers.length; i++) {
                HSSFCell cell = row.createCell(i);
                HSSFRichTextString text = new HSSFRichTextString(headers[i]);
                cell.setCellValue(text);
            }

            //在表中存放查询到的数据放入对应的列
            for (Student student : studentList) {
                System.out.println(student.toString());
                HSSFRow row1 = sheet.createRow(rowNum);
                row1.createCell(0).setCellValue(student.getName());
                row1.createCell(1).setCellValue(student.getProductLine());
                row1.createCell(2).setCellValue(student.getClassName());
                row1.createCell(3).setCellValue(student.getStatus());
                row1.createCell(4).setCellValue(student.getSex());
                row1.createCell(5).setCellValue(student.getPhone());
                row1.createCell(6).setCellValue(student.getQq());
                row1.createCell(7).setCellValue(student.getManager());
                row1.createCell(8).setCellValue(student.getHeadmaster());
                row1.createCell(9).setCellValue(student.getConsultant());
                row1.createCell(10).setCellValue(student.getStudyLocation());
                row1.createCell(11).setCellValue(student.getTotalFee());
                row1.createCell(12).setCellValue(student.getMonthlyDate());
                row1.createCell(13).setCellValue(student.getClassAmount());
                rowNum++;
            }
            response.setContentType("application/octet-stream");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName);
            response.flushBuffer();
            workbook.write(response.getOutputStream());
        }catch (IOException e){
            e.printStackTrace();
        }

        return new Result("导出成功",1);
    }
}
